from flask import Blueprint, request, jsonify, send_file
from openpyxl.styles import Alignment, Font
import pandas as pd
from io import BytesIO
from collections import OrderedDict
from utils.utils import build_fail_response, confirm_params_type
from database.db_tools import SQLServerDatabase, connection_string
# 创建蓝图
ts_recheck_all_detail_pre_page = Blueprint('ts_recheck_all_detail_pre_page', __name__)

# ts_recheck_all_detail_pre

@ts_recheck_all_detail_pre_page.route('/pyapi/getRecheckAllDetailPreByConditions', methods=['POST'])
def getRecheckAllDetailPreByConditions():
    try:
        orderNumber = request.json.get('orderNumber')
        checkRes = request.json.get('checkRes')
        recheckStartTime = request.json.get('recheckStartTime')
        recheckEndTime = request.json.get('recheckEndTime')
        pageindex = request.json.get('pageindex')
        pagesize = request.json.get('pagesize')
        # 动态拼接参数，支持多条件查询
        filterArr = []
        params = []
        if orderNumber:
            filterArr.append(f"orderNumber LIKE '%{orderNumber}%'")
            # params.append(orderNumber)
        if checkRes != '':
            filterArr.append(f"checkRes = {checkRes}")
            # params.append(checkRes)
        if recheckStartTime and recheckEndTime:
            filterArr.append(f"recheckStartTime >= '{recheckStartTime}' AND recheckEndTime <= '{recheckEndTime}'")
            # params.append(recheckStartTime)
            # params.append(recheckEndTime)

        filters = " AND ".join(filterArr)
        # if filters:
        #     filters += " AND isFinal = 1"
        # else:
        #     filters = ' isFinal = 1 '
        # 操作数据库
        with SQLServerDatabase(connection_string) as db:
            res = db.execute_query_pagination("ts_recheck_all_detail_pre", filters=filters, page=pageindex, page_size=pagesize)
            return jsonify(res)
    except Exception as e:
        return build_fail_response(message=str(e), params=confirm_params_type(request))


@ts_recheck_all_detail_pre_page.route('/pyapi/save_ts_recheck_all_pre', methods=['POST'])
def save_ts_recheck_all_pre():
    try:
        # 获取参数对象
        data = OrderedDict(sorted(request.json.items()))
        columns = []
        values = []
        # 封装key和value
        for key, value in data.items():
            columns.append(key)
            values.append(value)
        # 操作数据库
        with SQLServerDatabase(connection_string) as db:
            res = db.execute_insert("ts_recheck_all_detail_pre", values=values, columns=columns)
            return jsonify(res)
    except Exception as e:
        return build_fail_response(message=str(e), params=confirm_params_type(request))


# 导出复检数据
@ts_recheck_all_detail_pre_page.route('/pyapi/download_excel_by_rechcektime', methods=['POST'])
def download_excel():
    try:
        startDate = request.json.get('startDate')
        endDate = request.json.get('endDate')

        # 操作数据库
        with SQLServerDatabase(connection_string) as db:
            res = db.execute_query_all(
                "ts_recheck_all_detail_pre",
                filters="recheckStartTime >= ? AND recheckEndTime <= ?",
                params=[startDate, endDate],
                customSelectColumns="""
                    orderNumber, recheckEmployees, snStrLists, LEFT(recheckStartTime, 19) AS recheckStartTime, 
                    LEFT(recheckEndTime, 19) AS recheckEndTime, deviceVersion, softVersion, snTotal, failTotal, 
                    CASE 
                    WHEN failTotal = 0 THEN NULL  
                       ELSE 
               CASE 
                -- 如果结果为整数
                WHEN (CAST(failTotal AS FLOAT) / snTotal * 100) = FLOOR(CAST(failTotal AS FLOAT) / snTotal * 100) 
                THEN CAST(CAST(CAST(failTotal AS FLOAT) / snTotal * 100 AS INT) AS VARCHAR(50)) 
                -- 如果结果有小数，保留两位小数
                ELSE FORMAT(CAST(failTotal AS FLOAT) / snTotal * 100, '0.00') 
            END + '%'
                    END AS percentage ,
                    CASE
                    WHEN checkRes = 1 THEN '成功'
                    WHEN checkRes = 0 THEN '失败'
                    END AS checkRes
                """
            )

        # 将查询结果转换为 DataFrame
        df = pd.DataFrame(res['data'], columns=[
            'orderNumber', 'recheckEmployees', 'snStrLists', 'recheckStartTime', 'recheckEndTime',
            'deviceVersion', 'softVersion', 'snTotal', 'failTotal', 'percentage', 'checkRes'
        ])

        # 自定义表头
        custom_headers = {
            'orderNumber': '订单号',
            'recheckEmployees': '复检员工',
            'snStrLists': 'SN',
            'recheckStartTime': '复检开始时间',
            'recheckEndTime': '复检结束时间',
            'deviceVersion': '设备版本',
            'softVersion': '软件版本',
            'snTotal': '订单总数',
            'failTotal': '失败总数',
            'percentage': '失败占比',
            'checkRes': '复检结果',
        }

        # 替换表头
        df = df.rename(columns=custom_headers)

        # 使用 BytesIO 创建一个内存中的文件对象
        output = BytesIO()
        with pd.ExcelWriter(output, engine='openpyxl') as writer:
            df.to_excel(writer, index=False, sheet_name='复检数据')

            # 获取工作表对象
            worksheet = writer.sheets['复检数据']

            # 设置自定义列宽（单位是字符数）
            worksheet.column_dimensions['A'].width = 30  # '订单号'列
            worksheet.column_dimensions['B'].width = 15  # '复检员工'列
            worksheet.column_dimensions['C'].width = 30  # 'SN'列
            worksheet.column_dimensions['D'].width = 30  # '复检开始时间'列
            worksheet.column_dimensions['E'].width = 30  # '复检结束时间'列
            worksheet.column_dimensions['F'].width = 15  # '设备版本'列
            worksheet.column_dimensions['G'].width = 15  # '软件版本'列
            worksheet.column_dimensions['H'].width = 10  # '订单总数'列
            worksheet.column_dimensions['I'].width = 10  # '失败总数'列
            worksheet.column_dimensions['J'].width = 10  # '失败占比'列
            worksheet.column_dimensions['K'].width = 10  # '复检结果'列

            # 设置某列居中（如 '复检结果' 列）
            for row in worksheet.iter_rows(min_row=2, max_row=worksheet.max_row, min_col=4, max_col=11):
                for cell in row:
                    cell.alignment = Alignment(horizontal='center')

            # 设置 "失败总数" 列的单元格为红色，当值不为 0 时
            for row in worksheet.iter_rows(min_row=2, max_row=worksheet.max_row, min_col=11, max_col=11):
                for cell in row:
                    if cell.value == '失败':
                        cell.font = Font(color="FF0000")  # 设置字体颜色为红色

        output.seek(0)  # 将游标移动到文件开始位置

        # 返回文件作为下载
        return send_file(
            output,
            download_name='data.xlsx',  # 使用 download_name 替代 attachment_filename
            as_attachment=True,
            mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        )

    except Exception as e:
        return build_fail_response(message=str(e), params=confirm_params_type(request))
